package com.zhufei.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.zhufei.entity.News;
import com.zhufei.entity.Sponsor;

public class SponsorDao extends DBCon{
	private static String TABLE = "zhufei_sponsor";
	private static String ID = "ID";
	private static String NAME = "name";
	private static String AMOUNT = "amount";
	private static String DATETIME = "datetime";
	private static String HANDICAPPED = "handicapped";
	private static String PERSON_OR_COMPANY = "person_or_company";
	/**
	 * 向数据库插入一条赞助记录
	 * @param name  赞助者名字
	 * @param amount 赞助金额
	 * @param datetime 赞助时间，字符串格式为  yyyy-mm-dd hh:mm:ss
	 * @param handicapped  受助人名字
	 * @param mark 传入0表示个人赞助，1表示公司赞助
	 * @return true表示成功，false表示失败
	 */
	public static boolean insertSponsor(String name, int amount, String datetime, String handicapped, int mark){
		DBCon con = new DBCon();
		try {
			String id = GlobalsDao.getUUID();
			Statement statement = con.getStatement();
			String sql = "insert into " + TABLE + " ("+ID+","+NAME+","+AMOUNT+","+DATETIME+","+HANDICAPPED+","+PERSON_OR_COMPANY+") values ('"+id+"','"+name+"','"+amount+"','"+datetime+"','"+handicapped+"',"+mark+")";
			statement.execute(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 删除赞助
	 * @param id      赞助id
	 * @return  true表示成功，false表示失败
	 */
	public static boolean deleteSponsor(String id){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "delete from "+TABLE+" where ID ='"+id+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 修改赞助记录
	 * @param id 赞助id
	 * @param name  赞助者名字
	 * @param amount 赞助金额
	 * @param datetime 赞助时间，字符串格式为  yyyy-mm-dd hh:mm:ss
	 * @param handicapped 受助人名字
	 * @param mark 传入0表示个人赞助，1表示公司赞助
	 * @return true表示成功，false表示失败
	 */
	public static boolean updateSponsor(String id, String name, int amount, String datetime, String handicapped, int mark){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "update "+TABLE+" set "+NAME+"='"+name+"',"+AMOUNT+"='"+amount+"',"+DATETIME+"='"+datetime+"',"+HANDICAPPED+"='"+handicapped+"',"+PERSON_OR_COMPANY+"="+mark+" where "+ID+"='"+id+"'";
			statement.executeUpdate(sql);
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return false;
	}
	
	/**
	 * 按照时间降序的方式获取一定数量的Sponsor对象
	 * @param index 从第index条开始获取（第一条下标是0）
	 * @param number 获取Sponsor的数量，输入-1则获取数据库中所有Sponsor
	 * @return  赞助对象的ArrayList
	 */
	public static ArrayList<Sponsor> getSponsors(int index, int number){
		ArrayList<Sponsor> sponsorList = new ArrayList<Sponsor>();
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = null;
			if(number != -1){
				sql = "select * from "+ TABLE +" order by " + DATETIME+" limit "+index+","+number;
			}else{
				sql = "select * from "+ TABLE +" order by " + DATETIME;
			}
			
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()){
				Sponsor sponsor = new Sponsor();
				sponsor.setID(rs.getString(ID));
				sponsor.setName(rs.getString(NAME));
				sponsor.setAmount(rs.getInt(AMOUNT));
				sponsor.setDatetime(rs.getString(DATETIME));
				sponsor.setHandicapped(rs.getString(HANDICAPPED));
				sponsor.setPerson_or_company(rs.getInt(PERSON_OR_COMPANY));
				sponsorList.add(sponsor);
			}
			rs.close();
			return sponsorList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		}
		return null;
	}
	
	/**
	 * 获取数据库中赞助商数量总数
	 * @return  返回数据库有多少个赞助记录
	 */
	public static int getCountOfSponsors(){
		DBCon con = new DBCon();
		try {
			Statement statement = con.getStatement();
			String sql = "select count(*) as count from "+TABLE;
			ResultSet rs = statement.executeQuery(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				return count;
			}
			else 
				return 0;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			con.closeDB();
		} 
		return 0;
	}
}
